USE ASLB 
GO
IF (OBJECTPROPERTY ( OBJECT_ID('ChangeComponentState'), 'IsProcedure' ) IS NOT NULL)
BEGIN
   DROP PROC ChangeComponentState
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROC [dbo].[ChangeComponentState] 
       @DatabaseName Varchar(50) = NULL
      ,@ServerName   Varchar(50) = NULL
      ,@Disabled  bit
       
AS
/***********************************************************************************
** Name      : ChangeComponentState
**
** File Name : ChangeComponentState.sql
**
** Desc      : 
**
** Called By : 
**
** Calls     : 
**
** Returns   :
** Author    : jocaplan
**
** Date      : 04/02/2009
**
** Parameters: 
                

** Usage     :
               EXEC ChangeComponentState
                  @DatabaseName = NULL
                  ,@ServerName = 'COLTS'
                  ,@Disabled = 0

************************************************************************************
** Change History
************************************************************************************
** Date        Author     Description
** ----------  ---------  ----------------------------------------------------------
**********************************************************************************/

SET NOCOUNT ON

IF @ServerName IS NULL and @DatabaseName IS NOT NULL
BEGIN

   UPDATE [DATABASE]
   SET [Disabled] = @Disabled
   WHERE
      Name = @DatabaseName
      
   SELECT * FROM [Database]
   
   

END

IF @ServerName IS NOT NULL and @DatabaseName IS NULL
BEGIN
   
   UPDATE [Server]
   SET [Disabled] = @Disabled
   WHERE
      Name = @ServerName
      
   SELECT * FROM [Server]

END

IF @ServerName IS NOT NULL and @DatabaseName IS NOT NULL
BEGIN

   UPDATE ServerDatabase
   SET [Disabled] = @Disabled
   FROM
      ServerDatabase SD
      JOIN [Server] SE
         ON SD.ServerID = SE.ServerID
      JOIN [Database] D
         ON SD.DatabaseID = D.DatabaseID
   WHERE
      D.Name = @DatabaseName
      AND SE.Name = @ServerName
      
   SELECT * FROM ServerDatabase

END